package realtime.history;

import com.vividsolutions.jts.geom.Coordinate;
import com.vividsolutions.jts.geom.GeometryFactory;
import constant.DatabaseConstant;
import constant.FileLocation;
import constant.StopType;
import entity.geometry.MultiPolygonRing;
import entity.geometry.MultiPolygonStreetArea;
import entity.geometry.POI;
import entity.geometry.Point;
import entity.realtime.POIType;
import entity.realtime.Trip;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import realtime.statistic.ProcessTrip;
import redis.clients.jedis.Jedis;
import redis.clients.jedis.Pipeline;
import redis.clients.jedis.Response;
import utility.Serialize;
import utility.database.PreprocessPOIData;
import utility.database.SqlServerAccess;
import utility.file.PropertiesLoader;
import utility.geometry.ProcessGeoJson;
import utility.geometry.ProcessPoint;
import utility.time.ProcessTime;

import java.sql.*;
import java.util.*;

/**
 * Created by lit on 2018/4/18.
 */
public class ProcessHistoryTrip {

    /**
     * 数据连接
     */
    private Connection connection = null;
    private PreparedStatement preparedStatement = null;
    /*private SqlServerAccess mssql = null;*/

    /**
     * 用于POI
     */
    private ArrayList<Map<Long, HashSet<POI>>> poiIndexList = null;
    private PreprocessPOIData preprocessPOIData = null;

    /**
     * 用于输出信息
     */
    private Logger logger = null;

    /**
     * 用于获取街道区域、环路ID
     */
    private ArrayList<MultiPolygonStreetArea> streetAreaArray = null;
    private ArrayList<MultiPolygonRing> ringArray = null;
    /**
     * 存放POI类型的判断标准
     */
    private ArrayList<POIType> poiTypeArray = null;

    /**
     * 统计年月
     */
    private int year;
    private int month;
    private String datetime;

    /**
     * 用来获取Redis内存
     */
    private Jedis jedis;

    public ProcessHistoryTrip(){
        initPrivateField();
    }
    private boolean initMSSQL(){
        if( connection == null ){
            try {
                connection = SqlServerAccess.getInstance();
                connection.setAutoCommit(false);
            } catch (Exception e) {
                e.printStackTrace();
                return false;
            }
        }
        return true;
    }

    public void finishMSSQL(){
        if(connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(preparedStatement != null){
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(jedis != null){
            jedis.close();
        }
    }

    public void processTrip(String lastStatMonth, String statEndTime){

        logger.info("获取待处理的行程数据");
        Map<String,ArrayList<Trip>> pendingTripMap = getPendingTrip(datetime);
        if(pendingTripMap != null && pendingTripMap.size() > 0 ){

            year = ProcessTime.getYear(lastStatMonth);
            month = ProcessTime.getMonth(lastStatMonth);
            datetime = statEndTime;
            int num=0;
            long startTime = System.currentTimeMillis();
            logger.info("更新待处理行程起点和终点的位置及类型数据");
            for(String vid : pendingTripMap.keySet()){
                ArrayList<Trip> tripArray = pendingTripMap.get(vid);
                for(Trip trip : tripArray){
                    logger.debug("开始处理行程：{}",trip.getTripID());
                    locateTrip(trip);
                    num++;
                    long endTime = System.currentTimeMillis();
                    if(num%10 == 0){
                        logger.debug("Process {} vehicle consume {}ms",num,endTime-startTime);
                        startTime = endTime;
                    }
                }
            }
            logger.info("更新行程上下游关系");
            getPreNextTrip(pendingTripMap);
            addTrip(pendingTripMap);
        }
    }

    /**
     * 更新待处理的行程，除了与上下游关系以外的字段
     * @param pendingTripMap
     */
    private void addTrip(Map<String, ArrayList<Trip>> pendingTripMap) {
        if(initMSSQL()){
            String sql = "update rtm_trip set PlateNum=?,BoxWeight=?,TripStartPOI=?,TripStartPOIMatch=?,TripEndPOI=?,TripEndPOIMatch=?,TripTime=?,StartType=?,StartPOI=?,EndType=?,EndPOI=?,StartDist=?,EndDist=?,StartRing=?,EndRing=?,IsPOI=1 where ID=?";
            try{
                int num = 0;
                preparedStatement = connection.prepareStatement(sql);
                for(String vid : pendingTripMap.keySet()){
                    ArrayList<Trip> tripArray = pendingTripMap.get(vid);
                    for(Trip trip : tripArray){
                        preparedStatement.setString(1,trip.getPlateNum());
                        preparedStatement.setString(2,trip.getBoxWeight());
                        preparedStatement.setString(3,trip.getTripStartPOI());
                        preparedStatement.setInt(4,trip.getTripStartPOIMatch());
                        preparedStatement.setString(5,trip.getTripEndPOI());
                        preparedStatement.setInt(6,trip.getTripEndPOIMatch());
                        preparedStatement.setInt(7,trip.getTripTime());
                        if(trip.getStartType() != null){
                            preparedStatement.setInt(8,trip.getStartType());
                        }else{
                            preparedStatement.setNull(8, Types.INTEGER);

                        }
                        preparedStatement.setString(9,trip.getStartPOI());
                        if(trip.getEndType() != null){
                            preparedStatement.setInt(10,trip.getEndType());
                        }else{
                            preparedStatement.setNull(10,Types.INTEGER);
                        }
                        preparedStatement.setString(11,trip.getEndPOI());
                        preparedStatement.setString(12,trip.getStartDist());
                        preparedStatement.setString(13,trip.getEndDist());
                        preparedStatement.setString(14,trip.getStartRing());
                        preparedStatement.setString(15,trip.getStartRing());
                        preparedStatement.setLong(16,trip.getId());
                        preparedStatement.addBatch();
                        num++;
                        if(num % 100 == 0){
                            preparedStatement.executeBatch();
                        }
                    }
                }
                preparedStatement.executeBatch();
                connection.commit();
                logger.info("更新数据库中{}条行程数据",num);
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

    /**
     * 4.获取行程的上下游关系，并更新上下游相关字段PrevTripID,PrevTripEndTime,PrevStopTime,PrevDistance,PrevStopType,NextTripID,NextTripStartTime,NextStopTime,NextDistance,NextStopType,TripPurpose
     * 只有上游，上游行程更新Next相关字段。当前行程更新Prev相关字段；更新出行目的
     * 只有下游，下游行程更新Prev相关字段，当前行程更新Next相关字段；更新出行目的
     * 上下游都有
     * @param pendingTripMap
     */
    private void getPreNextTrip(Map<String, ArrayList<Trip>> pendingTripMap) {
        Map<String,TreeMap> uniformDataSource = getUniformDataSource(pendingTripMap);
        updatePreNextField(uniformDataSource,pendingTripMap);
        updateRedisData(uniformDataSource);
    }

    /**
     * 将新行程记录存入Redis中
     * @param uniformDataSource
     */
    private void updateRedisData(Map<String, TreeMap> uniformDataSource) {
        //将统一数据源uniformDataSource更新到Redis内存
        Pipeline pl = jedis.pipelined();

        for(String vid : uniformDataSource.keySet()){
            //批量删除Redis内存中已有的vid
            pl.del(vid);
        }
        pl.sync();
        for (String vid : uniformDataSource.keySet()){
            //批量插入
            TreeMap<String,Trip> tripTree = uniformDataSource.get(vid);
            //得到逆序trip
            TreeMap<String,Trip> tripTreeReverse = new TreeMap<>(Collections.reverseOrder());
            tripTreeReverse.putAll(tripTree);
            int i=0;
            for(String tripStartTime : tripTreeReverse.keySet()){
                if(i < 5 && tripTreeReverse.get(tripStartTime) != null){
                    String tripStr = new String(Serialize.serialize(tripTreeReverse.get(tripStartTime)));
                    //插入行程
                    pl.rpush(vid,tripStr);
                    //设置失效时间为一天
                    pl.expire(vid,86400);
                }else{
                    break;
                }
                i++;
            }
        }
        pl.sync();
    }

    /**
     * 更新上下游相关的字段，同时更新到数据库中
     * @param uniformDataSource
     * @param pendingTripMap
     */
    private void updatePreNextField(Map<String, TreeMap> uniformDataSource, Map<String, ArrayList<Trip>> pendingTripMap) {
        if(initMSSQL()) {
            //相邻两条行程中的上一条行程
            String sql = "update rtm_trip set NextTripID=?,NextTripStartTime=?,NextStopTime=?,NextDistance=?,NextStopType=?,TripPurpose=? where ID = ?;";
            //相邻两条行程中的下一条行程
            sql += "update rtm_trip set PrevTripID=?,PrevTripEndTime=?,PrevStopTime=?,PrevDistance=?,PrevStopType=? where ID = ?";
            try {
                preparedStatement = connection.prepareStatement(sql);

                int updateTripNum = 0;
                //检查待处理行程的上下游关系，更新相应行程字段
                for (String vid : pendingTripMap.keySet()) {
                    ArrayList<Trip> tripArray = pendingTripMap.get(vid);
                    TreeMap<String, Trip> tripTree = uniformDataSource.get(vid);
                    for (Trip currentTrip : tripArray) {
                        if(tripTree != null){
                            String prev = tripTree.lowerKey(currentTrip.getTripStartTime());
                            String next = tripTree.higherKey(currentTrip.getTripStartTime());
                            if (prev != null) {
                                //上一条行程
                                Trip prevTrip = tripTree.get(prev);
                                getPrevNextTripField(prevTrip, currentTrip, preparedStatement);
                                updateTripNum++;
                            }
                            if (next != null) {
                                //下一条行程
                                Trip nextTrip = tripTree.get(next);
                                getPrevNextTripField(currentTrip, nextTrip, preparedStatement);
                                updateTripNum++;
                            }
                            //将字段值更新完后的当前trip添加到统一数据源中
                            tripTree.put(currentTrip.getTripStartTime(), currentTrip);
                            if (updateTripNum % 100 == 0) {
                                preparedStatement.executeBatch();
                            }
                        }
                    }
                }
                //更新所有行程的上下游关系字段，这样在最后更新所有待处理行程的时候不需要再更新这些字段了
                preparedStatement.executeBatch();
                connection.commit();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 获取相邻两条trip互相关联字段的数据
     * 前一条trip更新Next相关字段，后一条trip更新Prev相关字段
     * @param beforeTrip 前一条
     * @param afterTrip 后一条
     */
    private void getPrevNextTripField(Trip beforeTrip, Trip afterTrip, PreparedStatement preparedStatement) {
        int stopTime = (int) ProcessTime.diffMinutes(afterTrip.getTripStartTime(),beforeTrip.getTripEndTime());
        int stopType = getStopType(stopTime,beforeTrip.getTripEndTime());
        double distance = ProcessPoint.getDistance(afterTrip.getTripStartLon(),afterTrip.getTripStartLat(),beforeTrip.getTripEndLon(),beforeTrip.getTripEndLat());
        int tripPurpose = -1;
        beforeTrip.setNextTripID(afterTrip.getTripID());
        beforeTrip.setNextTripStartTime(afterTrip.getTripStartTime());
        if(stopTime > 0){
            beforeTrip.setNextStopTime(stopTime);
            afterTrip.setPrevStopTime(stopTime);
        }
        if(stopType > 0){
            beforeTrip.setNextStopType(stopType);
            afterTrip.setPrevStopType(stopType);
        }
        if(distance > 0){
            beforeTrip.setNextDistance(distance);
            afterTrip.setPrevDistance(distance);
        }
        tripPurpose = getTripPurpose(beforeTrip);
        if(tripPurpose > 0){
            beforeTrip.setTripPurpose(tripPurpose);
        }
        afterTrip.setPrevTripID(beforeTrip.getTripID());
        afterTrip.setPrevTripEndTime(beforeTrip.getTripEndTime());

        try{
            preparedStatement.setLong(1,beforeTrip.getNextTripID());
            preparedStatement.setString(2,beforeTrip.getNextTripStartTime());
            if(beforeTrip.getNextStopTime() != null){
                preparedStatement.setInt(3,beforeTrip.getNextStopTime());
            }else{
                preparedStatement.setNull(3,Types.INTEGER);
            }
            if(beforeTrip.getNextDistance() != null){
                preparedStatement.setDouble(4,beforeTrip.getNextDistance());
            }else{
                preparedStatement.setNull(4,Types.DOUBLE);
            }
            if(beforeTrip.getNextStopType() != null){
                preparedStatement.setInt(5,beforeTrip.getNextStopType());
            }else{
                preparedStatement.setNull(5,Types.INTEGER);
            }
            if(beforeTrip.getTripPurpose() != null){
                preparedStatement.setInt(6,beforeTrip.getTripPurpose());
            }else{
                preparedStatement.setNull(6,Types.INTEGER);
            }
            preparedStatement.setLong(7,beforeTrip.getId());

            preparedStatement.setLong(8,afterTrip.getPrevTripID());
            preparedStatement.setString(9,afterTrip.getPrevTripEndTime());
            if(afterTrip.getPrevStopTime() != null){
                preparedStatement.setInt(10,afterTrip.getPrevStopTime());
            }else{
                preparedStatement.setNull(10,Types.INTEGER);
            }
            if(afterTrip.getPrevDistance() != null){
                preparedStatement.setDouble(11,afterTrip.getPrevDistance());
            }else{
                preparedStatement.setNull(11,Types.DOUBLE);
            }
            if(afterTrip.getPrevStopType() != null){
                preparedStatement.setInt(12,afterTrip.getPrevStopType());
            }else{
                preparedStatement.setNull(12,Types.INTEGER);
            }
            preparedStatement.setLong(13,afterTrip.getId());
            preparedStatement.addBatch();
        }catch (Exception e){
            e.printStackTrace();
        }

    }

    /**
     * 获取行程目的
     * 只判断家在六环内的车辆
     * @param trip
     */
    private int getTripPurpose(Trip trip) {
        //判断家在六环内的车，才有出行目的
        if(trip.getHome_ring() != null && Integer.parseInt(trip.getHome_ring()) < 121){
            int nextStopTime =-1,prevStopTime = -1;
            if(trip.getNextStopTime() != null){
                nextStopTime = trip.getNextStopTime();
            }
            if(trip.getPrevStopTime() != null){
                prevStopTime = trip.getPrevStopTime();
            }

            //获取出行在周几,1表示周日
            int tripWeekday = ProcessTime.getWeekday(trip.getTripStartTime());
            int startType = -1,endType = -1;
            if(trip.getStartType() != null){
                startType = trip.getStartType();;
            }
            if(trip.getEndType() != null){
                endType = trip.getEndType();
            }
            String tripStartTimeHour = trip.getTripStartTime().split(" ")[1];
            String tripEndTimeHour = trip.getTripEndTime().split(" ")[1];

            boolean isBetweenStartTime = ProcessTime.isBigger(tripStartTimeHour,"06:00:00") && ProcessTime.isBigger("08:30:00",tripStartTimeHour);
            boolean isBetweenEndTime = ProcessTime.isBigger(tripEndTimeHour,"15:30:00") && ProcessTime.isBigger("19:30:00",tripEndTimeHour);
            boolean isWeekday = (tripWeekday > 1 && tripWeekday < 7);
            //下一次行程和本次是在同一天
            boolean isNextTripSameDay = trip.getTripStartTime().split(" ")[1].equals(trip.getNextTripStartTime().split(" ")[1]);
            //上学 送
            if(0 < nextStopTime && nextStopTime <= 30  && isBetweenStartTime && isWeekday){
                if(endType == 4){return 1;}
                else if (endType == 5){return 2;}
                else if (endType == 6){return 3;}
            }
            //上学 接
            if(0 < prevStopTime && prevStopTime <= 30 && isBetweenEndTime && isWeekday){
                if(startType == 4){return 1;}
                else if (startType == 5){return 2;}
                else if (startType == 6){return 3;}
            }
            //通勤上下班
            if((startType == 1 && endType == 2) || (startType == 2 && endType == 1)){
                return 4;
            }
            //回幼儿园
            if(endType == 4){return 5;}
            //回小学
            if(endType == 5){return 6;}
            //回中学
            if(endType == 6){return 7;}
            //回单位
            if(endType == 2){return 8;}
            //就医
            if(endType == 7 && isNextTripSameDay){
                return 9;
            }
            //购物，下一次行程开始在22点前
            if(endType == 8 && isNextTripSameDay){
                boolean isBeforeTwenty = ProcessTime.isBigger("22:00:00",trip.getNextTripStartTime().split(" ")[0]);
                if(isBeforeTwenty){return 10;}
            }
            //休闲娱乐
            if(endType == 9){
                if(isWeekday){
                    //工作日行程开始在13点以后
                    if(ProcessTime.getHour(trip.getTripStartTime()) > 13){
                        return 10;
                    }
                }else{
                    return 10;
                }
            }
            //维修
            if(endType == 10 && trip.getNextStopTime() >= 2*24*60){
                //还有个1星期未到过此地的判断条件暂时不知道怎么判断出来
                return 11;
            }

            if (trip.getTripStartPOIMatch()!= null && trip.getTripEndPOIMatch() != null){
                //进京
                if(trip.getTripStartPOIMatch()==1 && (trip.getTripEndPOIMatch()==2||trip.getTripEndPOIMatch()==3)) {
                    return 12;
                }
                //出京
                if (trip.getTripEndPOIMatch()==1 && (trip.getTripStartPOIMatch()==2||trip.getTripStartPOIMatch()==3)) {
                    return 13;
                }
            }
            //临时停车
            if(0 < nextStopTime && nextStopTime <= 30){
                return 14;
            }
            //其他回家
            if(endType == 1){ return 15;}
            return 2000;
        }
        //什么都没匹配上，不是六环内的车
        return -1;
    }

    /**
     * 判断停车类型
     * @param stopTime 停车时长
     * @param tripEndTime 停车时间
     * @return
     */
    private int getStopType(int stopTime, String tripEndTime) {
        int stopType = -1;
        if(stopTime < 3*60){
            stopType = StopType.temporary;
        }else if(3*60 < stopTime && stopTime <= 24*60){
            int hour = ProcessTime.getHour(tripEndTime);
            if(hour > 6 && hour <= 18){
                stopType = StopType.day;
            }else{
                stopType = StopType.night;
            }
        }else{
            stopType = StopType.longTime;
        }
        return stopType;
    }

    /**
     * 从Redis和SqlServer获取统一的数据源
     * @param pendingTripMap
     * @return
     */
    private Map<String,TreeMap> getUniformDataSource(Map<String, ArrayList<Trip>> pendingTripMap) {
        //以VID为key，存放车辆的trip记录
        Map<String,TreeMap> redisResMap = new HashMap<>();
        Map<String,TreeMap> uniformDataSource = new HashMap<>();
        //需要去数据库查找最近数据的VID集合
        ArrayList<String> notInRedisVID = new ArrayList();

        Pipeline pl = jedis.pipelined();
        HashMap<String, Response<List<String>>> statusTmp = new HashMap<>();
        for( String vid : pendingTripMap.keySet() ){
            Response<List<String>> item = pl.lrange(vid, 0, -1);
            statusTmp.put(vid, item);
        }
        pl.sync();

        HashSet<String> visited = new HashSet<>();
        for(String vid : statusTmp.keySet() ){
            //以TripStartTime为key，存放单条trip记录
            TreeMap<String,Trip> redisTripTree = new TreeMap();
            List redisData = statusTmp.get(vid).get();
            if( redisData != null && redisData.size() > 0){
                //Redis中有该VID的数据，遍历list转成对象
                Iterator iterator = redisData.iterator();
                while(iterator.hasNext()){
                    Object temp = iterator.next();
                    if(temp != null && temp.toString().length()>0){
                        Trip trip = (Trip)Serialize.unserialize(temp.toString().getBytes());
                        redisTripTree.put(trip.getTripStartTime(),trip);
                    }
                }
                redisResMap.put(vid,redisTripTree);
                visited.add(vid);
            }
        }

        // 求差集，获得Redis中不存在的VID
        notInRedisVID.addAll(pendingTripMap.keySet());
        notInRedisVID.removeAll(visited);

        //从数据库中获取Redis内存中没有的VID的最近五条行程记录
        Map<String,TreeMap> databaseResMap = getNotInRedisVIDRes(notInRedisVID);
        //上两步获取的集合合并作为一个统计的数据源
        if(redisResMap != null){
            uniformDataSource.putAll(redisResMap);
        }
        if(databaseResMap != null){
            uniformDataSource.putAll(databaseResMap);
        }
        return uniformDataSource;
    }

    /**
     * 从数据库中获取Redis中没有的车辆的行程数据
     * @param notInRedisVID
     * @return
     */
    private Map<String,TreeMap> getNotInRedisVIDRes(ArrayList<String> notInRedisVID) {
        if(initMSSQL() && notInRedisVID.size() > 0){
            Map<String,TreeMap> databaseResMap = new HashMap<>();
            String sql = "SELECT ID,TripID,c.VID,TripStartLon,TripStartLat,TripEndLon,TripEndLat,TripStartTime,TripEndTime,PrevTripID,PrevTripEndTime,PrevStopTime,PrevDistance,PrevStopType,NextTripID,NextTripStartTime,NextStopTime,NextDistance,NextStopType,home_ring FROM (SELECT a.*,b.home_ring,row_number() OVER(partition BY a.VID ORDER BY TripStartTime DESC) as rank FROM [dbo].[rtm_trip] a LEFT JOIN IDBox_Master.dbo.stat_month_userPortrait b on a.VID = b.VID where a.VID in (";
            String sqlPlaceholder = "";
            for(int i = 0; i < notInRedisVID.size(); i++){
                if(i == 0){
                    sqlPlaceholder += "?";
                }else{
                    sqlPlaceholder += ",?";
                }
            }
            sql += sqlPlaceholder;
            sql += ") AND IsPOI = 1 AND b.StatYear=? AND b.StatMonth=? AND TripStartTime <?) c where c.rank < 6";
            try{
                preparedStatement = connection.prepareStatement(sql);
                for(int j = 0; j < notInRedisVID.size(); j++){
                    preparedStatement.setString(j+1,notInRedisVID.get(j));
                }
                preparedStatement.setInt(notInRedisVID.size()+1,year);
                preparedStatement.setInt(notInRedisVID.size()+2,month);
                preparedStatement.setString(notInRedisVID.size()+3,datetime);
                ResultSet rs = preparedStatement.executeQuery();
                while(rs.next()){
                    Trip trip = new Trip();
                    String vid = rs.getString("VID");
                    String tripStartTime = rs.getString("TripStartTime");
                    trip.setId(rs.getLong("ID"));
                    trip.setTripID(rs.getLong("TripID"));
                    trip.setVid(vid);
                    trip.setTripStartLon(rs.getDouble("TripStartLon"));
                    trip.setTripStartLat(rs.getDouble("TripStartLat"));
                    trip.setTripEndLon(rs.getDouble("TripEndLon"));
                    trip.setTripEndLat(rs.getDouble("TripEndLat"));
                    trip.setTripStartTime(tripStartTime);
                    trip.setTripEndTime(rs.getString("TripEndTime"));
                    trip.setPrevTripID(rs.getLong("PrevTripID"));
                    trip.setPrevTripEndTime(rs.getString("PrevTripEndTime"));
                    trip.setPrevStopTime(rs.getInt("PrevStopTime"));
                    trip.setPrevDistance(rs.getDouble("PrevDistance"));
                    trip.setPrevStopType(rs.getInt("PrevStopType"));
                    trip.setNextTripID(rs.getLong("NextTripID"));
                    trip.setNextTripStartTime(rs.getString("NextTripStartTime"));
                    trip.setNextStopTime(rs.getInt("NextStopTime"));
                    trip.setNextDistance(rs.getDouble("NextDistance"));
                    trip.setNextStopType(rs.getInt("NextStopType"));
                    trip.setHome_ring(rs.getString("home_ring"));

                    if(databaseResMap.get(vid) != null){
                        databaseResMap.get(vid).put(tripStartTime,trip);
                    }else{
                        TreeMap<String,Trip> databaseTripTree = new TreeMap();
                        databaseTripTree.put(tripStartTime,trip);
                        databaseResMap.put(vid,databaseTripTree);
                    }
                }
            }catch (Exception e){
                e.printStackTrace();
            }
            return databaseResMap;
        }
        return null;
    }

    /**
     * 0.初始化需要使用的全局私有变量
     * @param
     * @param
     */
    private void initPrivateField() {
        logger = LoggerFactory.getLogger(ProcessTrip.class);
        //获取poi坐标的序列化列表，用来确定居住地和工作的POI
        logger.info("获取POI基础数据");
        preprocessPOIData = new PreprocessPOIData();
        ArrayList<POI> pois = preprocessPOIData.loadPOIFile();
        poiIndexList = preprocessPOIData.generateIndex(pois);
        //获取街道区域环路的坐标信息，用于确定点所在的街道区域和环路
        logger.info("获取街道区域和环路基础数据");
        String streetAreaFilePath = PropertiesLoader.getParameter(FileLocation.STREETAREA_GEOJSON_FILE_KEY,FileLocation.STREETAREA_GEOJSON_FILE_DEFAULT);
        streetAreaArray = ProcessGeoJson.importStreetAreaGeojsonFile(streetAreaFilePath);
        String ringFilePath = PropertiesLoader.getParameter(FileLocation.RING_GEOJSON_FILE_KEY,FileLocation.RING_GEOJSON_FILE_DEFAULT);
        ringArray = ProcessGeoJson.importRingGeojsonFile(ringFilePath);
        poiTypeArray = getBasePOIType();
        logger.info("获取街道区域和环路基础数据完成");

        //获取待处理的VID在Redis内存中的数据
        String host = PropertiesLoader.getParameter(DatabaseConstant.REDIS_HOST_KEY,DatabaseConstant.REDIS_HOST_DEFAULT);
        String port = PropertiesLoader.getParameter(DatabaseConstant.REDIS_HISTORY_PORT_KEY,DatabaseConstant.REDIS_HISTORY_PORT_DEFAULT);
        jedis = new Jedis(host,Integer.parseInt(port));
    }

    /**
     * 获取POI类型判断的基本数据依据
     * @return
     */
    private ArrayList<POIType> getBasePOIType() {
        ArrayList<POIType> poiTypeArray = new ArrayList<>(8);
        if(initMSSQL()){
            String sql = "select TypeID,MinType,MaxType,TypeOrder,notIn from IDBox_Base.dbo.info_poitype";
            try{
                preparedStatement = connection.prepareStatement(sql);
                ResultSet rs = preparedStatement.executeQuery();
                while (rs.next()){
                    POIType poiType = new POIType();
                    poiType.setTypeID(rs.getInt("TypeID"));
                    poiType.setMinType(rs.getInt("MinType"));
                    poiType.setMaxType(rs.getInt("MaxType"));
                    poiType.setTypeOrder(rs.getInt("TypeOrder"));
                    poiType.setNotInType(rs.getString("notIn"));
                    poiTypeArray.add(poiType);
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        return poiTypeArray;
    }

    /**
     * 1.获取待处理的行程
     * @param datetime
     */
    private Map<String,ArrayList<Trip>> getPendingTrip(String datetime) {
        if(initMSSQL()){
            int num=0;
            //车辆VID为key，车辆的行程记录存放在数组中作为value
            Map<String,ArrayList<Trip>> tripMap = new HashMap<>(512);
            String sql = "SELECT ID,TripID,a.VID,TripStartLon,TripStartLat,TripEndLon,TripEndLat,TripStartTime,TripEndTime,b.platenum,c.BoxWeight,home_lon,home_lat,work_lon,work_lat,poi_home,poi_work,home_ring  FROM rtm_trip a " +
                    " LEFT JOIN IDBox_Base.dbo.info_vehicle b ON a.VID = b.vid" +
                    " LEFT JOIN (SELECT VID,BoxWeight,home_lon,home_lat,work_lon,work_lat,poi_home,poi_work,home_ring FROM IDBox_Master.dbo.stat_month_userPortrait WHERE StatYear = ? AND StatMonth = ?) as c on a.VID = c.VID WHERE  isPOI = -1 AND TripStartTime < ? AND TripStartTime >= DATEADD(MINUTE, -5, ?)";
            try{
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setInt(1,year);
                preparedStatement.setInt(2,month);
                preparedStatement.setString(3,datetime);
                preparedStatement.setString(4,datetime);
                ResultSet rs = preparedStatement.executeQuery();
                String vid;
                while(rs.next()){
                    Trip trip = new Trip();
                    trip.setId(rs.getLong("ID"));
                    trip.setTripID(rs.getLong("TripID"));
                    vid = rs.getString("VID");
                    trip.setVid(vid);
                    trip.setTripStartLon(rs.getDouble("TripStartLon"));
                    trip.setTripStartLat(rs.getDouble("TripStartLat"));
                    trip.setTripEndLon(rs.getDouble("TripEndLon"));
                    trip.setTripEndLat(rs.getDouble("TripEndLat"));
                    trip.setTripStartTime(rs.getString("TripStartTime"));
                    trip.setTripEndTime(rs.getString("TripEndTime"));
                    trip.setPlateNum(rs.getString("platenum"));
                    trip.setBoxWeight(rs.getString("BoxWeight"));
                    trip.setHome_lon(rs.getDouble("home_lon"));
                    trip.setHome_lat(rs.getDouble("home_lat"));
                    trip.setHome_ring(rs.getString("home_ring"));
                    trip.setWork_lon(rs.getDouble("work_lon"));
                    trip.setWork_lat(rs.getDouble("work_lat"));
                    trip.setPoi_home(rs.getString("poi_home"));
                    trip.setPoi_work(rs.getString("poi_work"));
                    if(tripMap.get(vid) != null){
                        tripMap.get(vid).add(trip);
                    }else{
                        ArrayList<Trip> tripArray = new ArrayList<>();
                        tripArray.add(trip);
                        tripMap.put(vid,tripArray);
                    }
                    num++;
                }
            }catch (Exception e){
                e.printStackTrace();
            }
            logger.info("获取到{}条行程数据",num);
            return tripMap;
        }
        return null;
    }

    /**
     * 2.定位行程开始/结束点，查找其POI，区域id，环路id及POIMatch
     * @param trip
     */
    private void locateTrip(Trip trip) {
        //行程耗时分钟数
        logger.debug("计算本次行程时耗");
        Long tripTime = ProcessTime.diffMinutes(trip.getTripEndTime(),trip.getTripStartTime());
        trip.setTripTime(tripTime.intValue());

        //获取起始点POI,区域id,环路id,POIMatch及POIType
        logger.debug("获取开始点坐标相关信息");
        getStartLocate(trip);
        logger.debug("获取结束点坐标相关信息");
        getEndLocate(trip);
    }

    /**
     * 获取起始点的定位信息
     * @param trip
     */
    private void getStartLocate(Trip trip) {
        String startPOIId = null;
        Point startPoint = new Point(trip.getTripStartLon(),trip.getTripStartLat());
        TreeMap startPOITree = preprocessPOIData.getPoi(poiIndexList,startPoint);
        if(startPOITree != null){
            //至多获取三个最近点POI
            Iterator iterator = startPOITree.entrySet().iterator();
            int num = 0;
            while(iterator.hasNext()){
                if(num < 3){
                    Map.Entry entry = (Map.Entry)iterator.next();
                    POI poi = (POI)entry.getValue();
                    String poiid = poi.getPoiid();
                    if(startPOIId == null){
                        startPOIId = poiid;
                    }else{
                        startPOIId += ","+poiid;
                    }
                    num++;
                }else{
                    break;
                }
            }
        }
        if(startPOIId != null){
            trip.setTripStartPOI(startPOIId);
        }
        //判断开始点类型，家/单位/学校/医院等
        getStartPointType(startPoint, trip);
        getStartAreaRing(startPoint,trip);
        //更新POIMatch
        int matchCode;

        if(trip.getStartRing() != null){
            //北京内
            if(trip.getStartPOI() != null){
                matchCode = 3;
            }else{
                matchCode = 2;
            }
        }else{
            matchCode = 1;
        }

        if(trip.getTripStartLon() < 0 && trip.getTripStartLat() < 0){
            //没有坐标
            matchCode = 0;
        }
        trip.setTripStartPOIMatch(matchCode);
    }

    /**
     * 获取开始点的环路和区域
     * @param startPoint
     * @param trip
     */
    private void getStartAreaRing(Point startPoint, Trip trip) {
        String distID = null,ringID = null;
        String poi = null;
        if(trip.getStartPOI() != null){
            poi = trip.getStartPOI();
        }else if(trip.getTripStartPOI() != null){
            poi = trip.getTripStartPOI().split(",")[0];
        }
        if(poi != null && poi.length() > 0){
            //可以从poi中提取
            distID = poi.substring(14,16);
            ringID = poi.substring(20,23);
        }else{
            MultiPolygonStreetArea multiPolygonStreetArea = getStreetArea(startPoint);
            if (multiPolygonStreetArea != null) {
                distID = multiPolygonStreetArea.getAreaID();
            }
            ringID = getRing(startPoint);
        }
        if(distID != null){
            if(distID == "03"){
                distID = "02";
            }else if(distID == "04"){
                distID = "01";
            }
            trip.setStartDist(distID);
        }
        if(ringID != null){
            trip.setStartRing(ringID);
        }
    }

    /**
     * 获取开始点的类型，家/学校/单位还是其他等等
     * @param point
     * @param trip 行程对象
     */
    private void getStartPointType(Point point, Trip trip) {
        int pointType;
        double lon = point.getLon();
        double lat = point.getLat();
        double home_lon = trip.getHome_lon();
        double home_lat = trip.getHome_lat();
        double work_lon = trip.getWork_lon();
        double work_lat = trip.getWork_lat();

        //判断是否是家
        if(Math.abs(lon - home_lon) < 0.013 && Math.abs(lat - home_lat) < 0.009){
            pointType = 1;
            trip.setStartType(pointType);
            if(trip.getPoi_home() != null){
                trip.setStartPOI(trip.getPoi_home());
            }
            return;
        }
        //判断是否是单位
        if(Math.abs(lon - work_lon) < 0.013 && Math.abs(lat - work_lat) < 0.009){
            pointType = 2;
            trip.setStartType(pointType);
            if(trip.getPoi_work() != null){
                trip.setStartPOI(trip.getPoi_work());
            }
            return;
        }

        String tripStartPOI = trip.getTripStartPOI();
        if(tripStartPOI != null){
            String[] tripStartPOIArray = tripStartPOI.split(",");
            for(String tripPOI : tripStartPOIArray){
                int poiType = Integer.parseInt(tripPOI.split("#")[0]);
                for(int i=0; i < poiTypeArray.size(); i++){
                    POIType pt = poiTypeArray.get(i);
                    //判断是否在区间内
                    if(poiType >= pt.getMinType() && poiType <= pt.getMaxType()){
                        //判断是否等于正好在区间内但不属于这个类型的特殊值
                        if(pt.getNotInType() != null){
                            String[] notInTypeArray = pt.getNotInType().split(",");
                            for(int j=0; j < notInTypeArray.length; j++){
                                if(poiType == Integer.parseInt(notInTypeArray[j])){
                                    pointType = 1000;
                                    trip.setStartType(pointType);
                                    trip.setStartPOI(tripPOI);
                                    return;
                                }
                            }
                        }
                        pointType = pt.getTypeID();
                        trip.setStartType(pointType);
                        trip.setStartPOI(tripPOI);
                        return;
                    }
                }
            }
            //有poi，但没有匹配到对应的类型，那就设为1000，代表点类型是其他,将最近的点作为其真正的开始点
            pointType = 1000;
            trip.setStartType(pointType);
            trip.setStartPOI(tripStartPOIArray[0]);
        }
    }

    /**
     * 获取结束点的定位信息
     * @param trip
     */
    private void getEndLocate(Trip trip) {
        String endPOIId = null;
        //获取结束点
        Point endPoint = new Point(trip.getTripEndLon(),trip.getTripEndLat());
        TreeMap endPOITree = preprocessPOIData.getPoi(poiIndexList,endPoint);

        if(endPOITree != null){
            //至多获取三个最近点POI
            Iterator iterator = endPOITree.entrySet().iterator();
            int num = 0;
            while(iterator.hasNext()){
                if(num < 3){
                    Map.Entry entry = (Map.Entry)iterator.next();
                    POI poi = (POI)entry.getValue();
                    String poiid = poi.getPoiid();
                    if(endPOIId == null){
                        endPOIId = poiid;
                    }else{
                        endPOIId += ","+poiid;
                    }
                    num++;
                }else{
                    break;
                }
            }
        }
        if(endPOIId != null){
            trip.setTripEndPOI(endPOIId);
        }
        getEndPointType(endPoint,trip);
        getEndAreaRing(endPoint,trip);
        //更新POIMatch
        int matchCode;
        if(trip.getEndRing() != null){
            //北京内
            if(trip.getEndPOI() != null){
                matchCode = 3;
            }else{
                matchCode = 2;
            }
        }else{
            matchCode = 1;
        }
        if(trip.getTripEndLon() < 0 && trip.getTripEndLat() < 0){
            //没有坐标
            matchCode = 0;
        }
        trip.setTripEndPOIMatch(matchCode);
    }


    private void getEndAreaRing(Point endPoint, Trip trip) {
        String distID = null,ringID = null;
        String poi = null;
        if(trip.getEndPOI() != null){
            poi = trip.getEndPOI();
        }else if(trip.getTripEndPOI() != null){
            poi = trip.getTripEndPOI().split(",")[0];
        }
        if(poi != null && poi.length() > 0){
            //可以从poi中提取
            distID = poi.substring(14,16);
            ringID = poi.substring(20,23);
        }else{
            MultiPolygonStreetArea multiPolygonStreetArea = getStreetArea(endPoint);
            if (multiPolygonStreetArea != null) {
                distID = multiPolygonStreetArea.getAreaID();
            }
            ringID = getRing(endPoint);
        }
        if(distID != null){
            if(distID == "03"){
                distID = "02";
            }else if(distID == "04"){
                distID = "01";
            }
            trip.setEndDist(distID);
        }
        if(ringID != null){
            trip.setEndRing(ringID);
        }

    }

    private void getEndPointType(Point point, Trip trip) {
        int pointType;
        double lon = point.getLon();
        double lat = point.getLat();
        double home_lon = trip.getHome_lon();
        double home_lat = trip.getHome_lat();
        double work_lon = trip.getWork_lon();
        double work_lat = trip.getWork_lat();

        //判断是否是家
        if(Math.abs(lon - home_lon) < 0.013 && Math.abs(lat - home_lat) < 0.009){
            pointType = 1;
            trip.setEndType(pointType);
            if(trip.getPoi_home() != null){
                trip.setEndPOI(trip.getPoi_home());
            }
            return;
        }
        //判断是否是单位
        if(Math.abs(lon - work_lon) < 0.013 && Math.abs(lat - work_lat) < 0.009){
            pointType = 2;
            trip.setEndType(pointType);
            if(trip.getPoi_work() != null){
                trip.setEndPOI(trip.getPoi_work());
            }
            return;
        }
        String tripEndPOI = trip.getTripEndPOI();
        if(tripEndPOI != null) {
            String[] tripEndPOIArray = tripEndPOI.split(",");
            for (String tripPOI : tripEndPOIArray) {
                int poiType = Integer.parseInt(tripPOI.split("#")[0]);
                for (int i = 0; i < poiTypeArray.size(); i++) {
                    POIType pt = poiTypeArray.get(i);
                    //判断是否在区间内
                    if (poiType >= pt.getMinType() && poiType <= pt.getMaxType()) {
                        //判断是否等于正好在区间内但不属于这个类型的特殊值
                        if (pt.getNotInType() != null) {
                            String[] notInTypeArray = pt.getNotInType().split(",");
                            for (int j = 0; j < notInTypeArray.length; j++) {
                                if (poiType == Integer.parseInt(notInTypeArray[j])) {
                                    pointType = 1000;
                                    trip.setEndType(pointType);
                                    trip.setEndPOI(tripPOI);
                                    return;
                                }
                            }
                        }
                        pointType = pt.getTypeID();
                        trip.setEndType(pointType);
                        trip.setEndPOI(tripPOI);
                        return;
                    }
                }
            }
            //有poi，但没有匹配到对应的类型，那就设为1000，代表点类型是其他,将最近的点作为其真正的开始点
            pointType = 1000;
            trip.setEndType(pointType);
            trip.setEndPOI(tripEndPOIArray[0]);
        }
    }

    /**
     * ps2:获取点所在的街道和区域
     * @param point
     * @return
     */
    private MultiPolygonStreetArea getStreetArea(Point point){
        double x = point.getLon(), y = point.getLat();

        GeometryFactory geometryFactory = new GeometryFactory();
        Coordinate coordinate = new Coordinate(x,y);
        com.vividsolutions.jts.geom.Point geomPoint = geometryFactory.createPoint(coordinate);

        for(int i = 0; i < streetAreaArray.size(); i++){
            MultiPolygonStreetArea multiPolygonStreetArea = streetAreaArray.get(i);
            if(ProcessPoint.polygonContainsPoint(geomPoint,multiPolygonStreetArea.getMultiPolygon())){
                return multiPolygonStreetArea;
            }
        }
        return null;
    }


    /**
     * ps3:获取点所在的环路
     * @param point
     * @return
     */
    private String getRing(Point point){
        double x = point.getLon(), y = point.getLat();

        GeometryFactory geometryFactory = new GeometryFactory();
        Coordinate coord = new Coordinate(x,y);
        com.vividsolutions.jts.geom.Point geomPoint = geometryFactory.createPoint(coord);

        for(int i = 0; i < ringArray.size(); i++){
            MultiPolygonRing multiPolygonRing = ringArray.get(i);
            if(ProcessPoint.polygonContainsPoint(geomPoint,multiPolygonRing.getRingMultiPolygon())){
                return multiPolygonRing.getRingID();
            }
        }
        return null;
    }
}
